﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Youz.Common.DBUtility;
using Youz.Common.Entity;
using Youz.CreditRepay.Entity;

namespace Youz.CreditRepay.DAL
{
    /// <summary>
    /// Member_Certified Data Class
    /// </summary>
    public partial class Member_CertifiedDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Member_Certified";

        public Member_CertifiedDAL()
        { }

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Member_Certified model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into [Member_Certified](");
            strSql.Append("[CertifiedID],[Name],[CredentialsType],[CredentialsNum],[Img],[Status],[Memo],[Auditor],[AuditOpinion],[MemberAccount],[CreateTime],[CardNum],[Address],[Phone]");
            strSql.Append(") values (");
            strSql.Append("@CertifiedID,@Name,@CredentialsType,@CredentialsNum,@Img,@Status,@Memo,@Auditor,@AuditOpinion,@MemberAccount,@CreateTime,@CardNum,@Address,@Phone");
            strSql.Append(")");
            SqlParameter[] parameters = {
                  
                    new SqlParameter("@CertifiedID", model.CertifiedID),
                    new SqlParameter("@Name", model.Name),
                    new SqlParameter("@CredentialsType", model.CredentialsType),
                    new SqlParameter("@CredentialsNum", model.CredentialsNum),
                    new SqlParameter("@Img", model.Img),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@Auditor", model.Auditor),
                    //new SqlParameter("@AuditTime", model.AuditTime),
                    new SqlParameter("@AuditOpinion", model.AuditOpinion),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@CardNum", model.CardNum),
                    new SqlParameter("@Address", model.Address),
                    new SqlParameter("@Phone", model.Phone)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Member_Certified model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Member_Certified] set ");
            strSql.Append("[Name]=@Name,[CredentialsType]=@CredentialsType,[CredentialsNum]=@CredentialsNum,[Img]=@Img,[Status]=@Status,[Memo]=@Memo,[Auditor]=@Auditor,[AuditTime]=@AuditTime,[AuditOpinion]=@AuditOpinion,[MemberAccount]=@MemberAccount,[CreateTime]=@CreateTime,[CardNum]=@CardNum,[Address]=@Address,[Phone]=@Phone");
            strSql.Append(" where [CertifiedID]=@CertifiedID");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@CertifiedID", model.CertifiedID),
                    new SqlParameter("@Name", model.Name),
                    new SqlParameter("@CredentialsType", model.CredentialsType),
                    new SqlParameter("@CredentialsNum", model.CredentialsNum),
                    new SqlParameter("@Img", model.Img),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@Auditor", model.Auditor),
                    new SqlParameter("@AuditTime", model.AuditTime),
                    new SqlParameter("@AuditOpinion", model.AuditOpinion),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@CardNum", model.CardNum),
                    new SqlParameter("@Address", model.Address),
                    new SqlParameter("@Phone", model.Phone)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(Guid CertifiedID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Member_Certified] ");
            strSql.Append(" where [CertifiedID]=@CertifiedID");

            SqlParameter[] parameters = {
			
                new SqlParameter("@CertifiedID", CertifiedID)             
              
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Certified Get(Guid CertifiedID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Certified] ");
            strSql.Append(" where [CertifiedID]=@CertifiedID");

            SqlParameter[] parameters = {
			
                new SqlParameter("CertifiedID", CertifiedID)             
              
            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public Member_Certified Get(string memberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Certified] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount ");

            SqlParameter[] parameters = {
			
                new SqlParameter("MemberAccount", memberAccount)             
              
            };

            strSql.Append(" order by CreateTime desc ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Certified Get(DataRow row)
        {
            if (row == null) return null;

            Member_Certified model = new Member_Certified();


            if (row["CertifiedID"] != DBNull.Value)
            {
                model.CertifiedID = (Guid)row["CertifiedID"];
            }

            if (row["Name"] != DBNull.Value)
            {
                model.Name = (String)row["Name"];
            }

            if (row["CredentialsType"] != DBNull.Value)
            {
                model.CredentialsType = (String)row["CredentialsType"];
            }

            if (row["CredentialsNum"] != DBNull.Value)
            {
                model.CredentialsNum = (String)row["CredentialsNum"];
            }

            if (row["Img"] != DBNull.Value)
            {
                model.Img = (String)row["Img"];
            }

            if (row["Status"] != DBNull.Value)
            {
                model.Status = (AuditStatus)row["Status"];
            }

            if (row["Memo"] != DBNull.Value)
            {
                model.Memo = (String)row["Memo"];
            }

            if (row["Auditor"] != DBNull.Value)
            {
                model.Auditor = (String)row["Auditor"];
            }

            if (row["AuditTime"] != DBNull.Value)
            {
                model.AuditTime = (DateTime)row["AuditTime"];
            }

            if (row["AuditOpinion"] != DBNull.Value)
            {
                model.AuditOpinion = (String)row["AuditOpinion"];
            }

            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }

            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }

            if (row["CardNum"] != DBNull.Value)
            {
                model.CardNum = (String)row["CardNum"];
            }

            if (row["Address"] != DBNull.Value)
            {
                model.Address = (String)row["Address"];
            }

            if (row["Phone"] != DBNull.Value)
            {
                model.Phone = (String)row["Phone"];
            }


            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from [Member_Certified] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Member_Certified] ");

            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Member_Certified] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT * FROM [Member_Certified]  ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by CreateTime desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        #endregion  成员方法

        #region 新增

        /// <summary>
        ///撤销申请
        /// </summary>
        public bool Cancel(Member_Certified model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Member_Certified set ");
            strSql.Append("Status=@Status ");
            strSql.Append(" where CertifiedID=@CertifiedID ");
            SqlParameter[] parameters = {
				
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@CertifiedID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.Status;
            parameters[1].Value = model.CertifiedID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 审核
        /// </summary>
        public bool Audit(Member_Certified model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Member_Certified set ");
            strSql.Append("Status=@Status,");
            strSql.Append("Auditor=@Auditor,");
            strSql.Append("AuditTime=@AuditTime,");
            strSql.Append("AuditOpinion=@AuditOpinion ");
            strSql.Append(" where CertifiedID=@CertifiedID ");
            SqlParameter[] parameters = {			
					new SqlParameter("@Status", SqlDbType.Int,4),					
					new SqlParameter("@Auditor", SqlDbType.NVarChar,200),
					new SqlParameter("@AuditTime", SqlDbType.DateTime),
					new SqlParameter("@AuditOpinion", SqlDbType.NVarChar,200),
					new SqlParameter("@CertifiedID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.Status;
            parameters[1].Value = model.Auditor;
            parameters[2].Value = model.AuditTime;
            parameters[3].Value = model.AuditOpinion;
            parameters[4].Value = model.CertifiedID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个对象实体(获取该用户最后一次认证数据)
        /// </summary>
        public Member_Certified GetCertified(string MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Certified] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount and Status=2 order by createtime desc");

            SqlParameter[] parameters = {
			
                new SqlParameter("@MemberAccount", MemberAccount)             
              
            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion
    }
}
